﻿Imports System.Data
Imports System.Data.SqlClient
Public Class Frm_Equipe_Travail
    Public num As Integer = -1
    Public numf As Integer = -1
    Public modif As Integer = 0
    Dim row As DataRow

    Public Sub vider()
        txt_Remunération.Clear()
        txt_Remunération.Text = 0
        txt_tache.Clear()
        txt_remarq.Clear()
        txt_de.Clear()
        txt_a.Clear()
        If Comb_Personnels.Items.Count > 0 Then
            Comb_Personnels.SelectedIndex = 0
        End If

        If Comb_catégor.Items.Count > 0 Then
            Comb_catégor.SelectedIndex = 0
        End If

        For Each i As ListViewItem In List_tous.Items
            i.Selected = False
        Next

    End Sub
    Public Sub remplirDataset()
        '-----------------------------------------------------------
        Try

            dss.Tables("Equipe").Rows.Clear()
            Cmd = New SqlCommand("select * from Personnels, Personnels_Travail where Personnels.Id_Personnels=Personnels_Travail.Id_Personnels and id_dmande=" & id_event, Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Do While Dr.Read()
                    row = dss.Tables("Equipe").NewRow()
                    row("Nom Complet") = Dr("Nom") & "  " & Dr("Prenom")
                    row("Taches") = Dr("Taches")
                    row("Catégorie") = Dr("Categorie")
                    dss.Tables("Equipe").Rows.Add(row)
                Loop
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        '------------------------------------------------------------
    End Sub

    Public Sub affiche()
        Try
            List_tous.Items.Clear()
            Cmd = New SqlCommand("select * from Personnels, Personnels_Travail where Personnels.Id_Personnels=Personnels_Travail.Id_Personnels and id_dmande=" & id_event, Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows Then
                Do While Dr.Read()
                    Dim Line As ListViewItem = New ListViewItem(New String() {Dr("Nom") & "  " & Dr("Prenom"), Dr("Categorie"), Dr("Remuneration"), Dr("Taches"), Dr(19), Dr("De") & " / " & Dr("A"), Dr("id_p")})
                    List_tous.Items.Add(Line)
                Loop
            End If
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub Frm_Equipe_Travail_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        txt_Remunération.Text = 0

        '--------------------------------------------------------------------------'
        Try
            Comb_Personnels.Items.Clear()
            Cmd = New SqlCommand("Select * From Personnels", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()

                Comb_Personnels.Items.Add(Dr("Nom") & "  " & Dr("Prenom"))

            Loop
            Dr.Close()
            cnClose()
            If Comb_Personnels.Items.Count > 0 Then
                Comb_Personnels.SelectedIndex = 0
            End If

        Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Private Sub btn_nouv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nouv.Click
        vider()
    End Sub

    Private Sub btn_ajouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ajouter.Click
        Try
            Cmd = New SqlCommand("Select * From Personnels", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()

                If (Dr("Nom") & "  " & Dr("Prenom")) = Comb_Personnels.SelectedItem.ToString Then
                    num = Dr("Id_Personnels")
                End If
            Loop
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try

        If txt_client.Text <> "" And txt_date_evenement.Text <> "" Then
            If MsgBox("Vous Voulez Vraiment Ajouter ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Ajouter") = MsgBoxResult.Yes Then
                Try
                    Cmd = New SqlCommand("insert into Personnels_Travail (Id_Personnels,id_dmande,Remuneration,Taches,De,A,Remarque)" & _
                    "values (@Id_Personnels,@id_demande,@Remuneration,@Taches,@De,@A,@Remarque)", Con)

                    Cmd.Parameters.Add("@Id_Personnels", SqlDbType.Int).Value = num
                    Cmd.Parameters.Add("@id_demande", SqlDbType.Int).Value = id_event
                    Cmd.Parameters.Add("@Remuneration", SqlDbType.Float).Value = CDbl(txt_Remunération.Text)
                    Cmd.Parameters.Add("@Taches", SqlDbType.Text).Value = txt_tache.Text
                    Cmd.Parameters.Add("@De", SqlDbType.VarChar).Value = txt_de.Text
                    Cmd.Parameters.Add("@A", SqlDbType.VarChar).Value = txt_a.Text
                    Cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = txt_remarq.Text

                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()
                    MsgBox("Ajouté Avec Succes", MsgBoxStyle.Information, "Réussi !")
                    vider()
                    affiche()
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                    cnClose()
                End Try
            End If
        End If
    End Sub

    Private Sub Comb_Personnels_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Comb_Personnels.SelectedIndexChanged
        If Con.State = ConnectionState.Open Then
            cnClose()
        End If

        Try
            Comb_catégor.Items.Clear()
            Cmd = New SqlCommand("Select Categorie From Personnels Where (Nom + '  ' + Prenom) ='" & Comb_Personnels.SelectedItem.ToString & "'", Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows Then
                Do While Dr.Read
                    Comb_catégor.Items.Add(Dr("Categorie"))
                Loop
            End If

            Dr.Close()
            cnClose()

            If Comb_catégor.Items.Count > 0 Then
                Comb_catégor.SelectedIndex = 0
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub btn_supp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp.Click

        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(6).Text)
                Next
                If MsgBox("Voulez Vous Vraiment Supprimer", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Supprission !") = MsgBoxResult.Yes Then
                    Try
                        Cmd = New SqlCommand("Delete From Personnels_Travail Where id_p =" & n, Con)
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()
                        modif = 0
                        MsgBox("Suppresion effectué !", MsgBoxStyle.Information, "Terminer")
                        vider()
                        affiche()

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                End If
            Else
                MsgBox("Vous n'avez selectionner aucun ligne !", MsgBoxStyle.Critical, "Réssayer !")
            End If
        End With

    End Sub

    Private Sub List_tous_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles List_tous.SelectedIndexChanged
        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(6).Text)
                    modif = CInt(.Items(.SelectedIndices(i)).SubItems(6).Text)
                    Comb_Personnels.Text = (.Items(.SelectedIndices(i)).SubItems(0).Text)
                    Comb_catégor.Text = (.Items(.SelectedIndices(i)).SubItems(1).Text)
                    txt_tache.Text = (.Items(.SelectedIndices(i)).SubItems(3).Text)
                    txt_Remunération.Text = (.Items(.SelectedIndices(i)).SubItems(2).Text)
                    txt_remarq.Text = (.Items(.SelectedIndices(i)).SubItems(4).Text)
                Next
                Try
                    Cmd = New SqlCommand("Select De,A From Personnels_Travail Where id_p =" & n, Con)
                    open()
                    Cmd.ExecuteNonQuery()

                    Dr = Cmd.ExecuteReader()
                    If Dr.HasRows Then
                        Dr.Read()
                        txt_de.Text = Dr("De")
                        txt_a.Text = Dr("A")
                    End If
                    Dr.Close()
                    cnClose()
                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                    cnClose()
                End Try

            End If
        End With
    End Sub

    Private Sub btn_modif_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_modif.Click
        If modif <> 0 Then
            If txt_client.Text <> "" And txt_date_evenement.Text <> "" Then
                If MsgBox("Vous Voulez Vraiment Modifier ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Modifier !") = MsgBoxResult.Yes Then

                    Try
                        Cmd = New SqlCommand("Select * From Personnels", Con)
                        open()
                        Dr = Cmd.ExecuteReader()
                        Do While Dr.Read()

                            If (Dr("Nom") & "  " & Dr("Prenom")) = Comb_Personnels.SelectedItem.ToString Then
                                numf = Dr("Id_Personnels")
                            End If
                        Loop
                        Dr.Close()
                        cnClose()
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try

                    Try
                        Cmd = New SqlCommand("update Personnels_Travail set Id_Personnels=@Id_Personnels, Remuneration=@Remuneration,Taches=@Taches,De=@De,A=@A,Remarque=@Remarque" & _
                        " Where id_p=" & modif, Con)

                        Cmd.Parameters.Add("@Id_Personnels", SqlDbType.Int).Value = numf

                        Cmd.Parameters.Add("@Remuneration", SqlDbType.Float).Value = txt_Remunération.Text
                        Cmd.Parameters.Add("@Taches", SqlDbType.Text).Value = txt_tache.Text
                        Cmd.Parameters.Add("@De", SqlDbType.VarChar).Value = txt_de.Text
                        Cmd.Parameters.Add("@A", SqlDbType.VarChar).Value = txt_a.Text
                        Cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = txt_remarq.Text


                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()

                        modif = 0
                        MsgBox("Modification a Réussi ", MsgBoxStyle.Information, "Réussi !")
                        vider()
                        affiche()
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try
                End If
            End If
        End If
    End Sub

    Private Sub txt_Remunération_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_Remunération.TextChanged
        If txt_Remunération.Text = "" Then
            txt_Remunération.Text = 0
        End If
    End Sub
    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        Frm_liste_Demande.MdiParent = Frm_Main
        Frm_liste_Demande.Show()
        liste = "Pers"
    End Sub

    Private Sub ButtonX2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX2.Click
        RechPersonl = "Equipe_Travail"
        Me.Enabled = True
        Frm_Recherche_Personnels.Show()
    End Sub

    Private Sub btn_affi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_affi.Click
        If txt_client.Text <> "" And txt_date_evenement.Text <> "" And List_tous.Items.Count > 0 Then
            Try
                remplirDataset()
                datEven = txt_date_evenement.Text
                lieu = txt_lieu.Text
                N_J = jour_nuit.Text
                bon = "Equipe de travail"
                frm_bon.MdiParent = Frm_Main
                frm_bon.Show()
                Me.Close()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                cnClose()
            End Try
        End If
    End Sub
End Class